﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using SocialIn.Core.Data;
using System.Data;
using SocialIn.Biz.Utils;
using System.Web;

namespace SocialIn.Biz
{
    public sealed class Join
    {
        ConnectionStringSettings _BaseDBCon = null;
        SendMailProvider mailProvider;


        public Join()
        {
            _BaseDBCon = ConfigurationManager.ConnectionStrings[ConfigName.DBConfig];
            mailProvider = new SendMailProvider();
        }

        /// <summary>
        /// 회원가입을 등록합니다.
        /// </summary>
        /// <returns>정상처리 뮤무</returns>
        public bool AddMember(UserInfo userinfo)
        {
            StringBuilder query = new StringBuilder();

            // 회원 등록
            query.Append("DECLARE @Result VARCHAR(50);");
            query.Append("EXEC @Result = [dbo].[CreateMember]");
            query.Append("@USERID,@USERNAME, @PASSWORD,@EMAIL,@Roles;");
            query.Append("SELECT @RESULT;");

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userinfo.UserID);
            parameters.Add("@USERNAME", userinfo.UserName);
            parameters.Add("@PASSWORD", SecurityUtils.Encrypt(userinfo.Password, ConfigName.PasswordFormat));
            parameters.Add("@EMAIL", userinfo.Email);
            parameters.Add("@Roles", userinfo.UserType);

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<object> dtResult = sql.ExecuteScalar(query.ToString(), parameters);

            string invitationkey = dtResult.ReturnValue.ToString();
            if (string.IsNullOrEmpty(invitationkey))
                return false;

            //invitationkey = HttpUtility.UrlEncode(SecurityUtils.Base64Encode(string.Format("key={0}&id={1}", invitationkey, userinfo.UserID), Encoding.UTF8));
            invitationkey = SecurityUtils.Base64Encode(invitationkey, Encoding.UTF8);
            mailProvider.Send(new SendMailInfo()
            {
                Subject = Resources.MailResource.ResourceManager.GetString("Join_Subject"),
                Content = string.Format(Resources.MailResource.ResourceManager.GetString("Join_Content"), userinfo.UserID, invitationkey, userinfo.UserID, ConfigurationManager.AppSettings[ConfigName.HOME]),
                To = new MailAddress() { Email = userinfo.Email, DisplayName = userinfo.UserName }
            });

            return true;
            //invitationkey = dtResult.ReturnValue.ToString();
        }


        public bool CheckUserID(string userId)
        {
            StringBuilder query = new StringBuilder();

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userId);
            // 아이디 체크
            query.Append("EXEC [dbo].[CheckUserID]");
            query.Append("@USERID;");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<object> dtResult = sql.ExecuteScalar(query.ToString(), parameters);

            if (Convert.ToInt32(dtResult.ReturnValue) > 0)
                return false;

            return true;
        }

        public bool ChangePassword(string oldpass,string pass, string userid)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = "UPDATE MEMBER SET PASSWORD = @PASSWORD WHERE USERID = @USERID AND PASSWORD = @OLDPASSWORD;";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);
            parameters.Add("@PASSWORD", SecurityUtils.Encrypt(pass, ConfigName.PasswordFormat));
            parameters.Add("@OLDPASSWORD", SecurityUtils.Encrypt(oldpass, ConfigName.PasswordFormat));

            Result<int> dtResult = sql.ExecuteNonQuery(query, parameters);

            if (dtResult.ReturnValue < 0)
                return false;

            return true;
        }


        public int IsVerifyMember(string key, string userid)
        {
            StringBuilder query = new StringBuilder();

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@AUTHKEY", key);
            parameters.Add("@USERID", userid);

            query.Append("EXEC [dbo].[VerifyMember]");
            query.Append("@AUTHKEY,@USERID;");

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            Result<object> dtResult = sql.ExecuteScalar(query.ToString(), parameters);

            return Convert.ToInt32(dtResult.ReturnValue);
        }

        public DataTable GetUserInfo(string userId)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = "SELECT USERID, USERNAME, EMAIL,SELFINTRODUCTION, PICTUREPATH, ADDRESS , PHONENUM FROM MEMBER WHERE USERID= @USERID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userId);

            Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);

            return dtResult.ReturnValue;
        }

        public DataTable GetMyTalentList(string userid)
        {

            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = @"SELECT A.SUBCATEGORY, A.CODENAME, A.ETC1, A.ETC2 , CASE WHEN B.TALENTID = A.SUBCATEGORY THEN 1 ELSE 0 END AS CHECKED
	                            FROM (SELECT SUBCATEGORY, CODENAME, ETC1, ETC2
								                            FROM CODEMASTER
								                            WHERE CATEGORY = 'M003') A Left JOIN
							                            (SELECT TALENTID
								                            FROM MYTALENT
								                            WHERE USERID = @USERID) B 
		                            ON A.SUBCATEGORY = B.TALENTID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);

            Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);

            return dtResult.ReturnValue;
        }

        public bool UpdateUser(UserInfo userinfo)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = @"UPDATE MEMBER
			                        SET USERNAME = @USERNAME,
			                        SELFINTRODUCTION = @SELFINTRODUCTION,
			                        ADDRESS = @ADDRESS,
			                        PHONENUM = @PHONENUM 
		                        WHERE USERID = @USERID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERNAME", userinfo.UserName);
            parameters.Add("@SELFINTRODUCTION", userinfo.SelfIntroduction);
            parameters.Add("@ADDRESS", userinfo.Address);
            parameters.Add("@PHONENUM", userinfo.PhoneNum);
            parameters.Add("@USERID", userinfo.UserID);

            Result<int> dtResult = sql.ExecuteNonQuery(query, parameters);

            if (dtResult.ReturnValue < 0)
                return false;

            return true;
        }

        public void UpdateTalent(List<string> talentList, string userid)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = "EXEC [dbo].[AddTalent] @USERID, '{0}';";

            StringBuilder sbquery = new StringBuilder();
            sbquery.Append("DELETE MYTALENT WHERE USERID = @USERID;");

            for (int i = 0; i < talentList.Count; i++)
            {
                sbquery.Append(string.Format(query, talentList[i]));
            }


            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);

            Result<int> dtResult = sql.ExecuteNonQuery(sbquery.ToString(), parameters);
        }

        public DataTable GetTalentList(string userid)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);
            string query = @"select A.CodeName, A.Etc1, A.Etc2
	                            from (
			                            SELECT SubCategory, CodeName, Etc1, Etc2
				                            FROM CODEMASTER 
				                            WHERE CATEGORY = 'M003' ) A, (
							                            SELECT TALENTID
								                            FROM MYTALENT
								                            WHERE USERID = 'TESTUSER') B
	                            where A.SubCategory = B.TALENTID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);
            Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);


            return dtResult.ReturnValue;
        }

        public void UpDateProFileImage(string userid, string filePath)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);

            string query = "UPDATE MEMBER SET PICTUREPATH = @PICTUREPATH WHERE USERID = @USERID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);
            parameters.Add("PICTUREPATH", filePath);

            Result<int> dtResult = sql.ExecuteNonQuery(query, parameters);
        }

        public DataTable GetMyGiveList(string userid)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);

            string query = "EXEC [dbo].[GetMyGiveList] @USERID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);

            Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);

            return dtResult.ReturnValue;
        }

        public DataTable GetMyGiveHistory(string userid)
        {
            SqlHelper sql = new SqlHelper(_BaseDBCon.ProviderName, _BaseDBCon.ConnectionString);

            string query = "EXEC [dbo].[GetMyGiveHistory] @USERID";

            Dictionary<string, string> parameters = new Dictionary<string, string>();
            parameters.Add("@USERID", userid);

            Result<DataTable> dtResult = sql.ExecuteDataTable(query, parameters);

            return dtResult.ReturnValue;
        }
    }
}
